﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

namespace NKD07.Classes
{
    public class warehouse:database
    {
        private int id;

        public int Id
        {
            get { return id; }
            set { id = value; }
        }

        private int idManufacturer;

        public int IdManufacturer
        {
            get { return idManufacturer; }
            set { idManufacturer = value; }
        }

        private int idProduct;

        public int IdProduct
        {
            get { return idProduct; }
            set { idProduct = value; }
        }

        private DateTime datetime;

        public DateTime Datetime
        {
            get { return datetime; }
            set { datetime = value; }
        }

        private int stock;

        public int Stock
        {
            get { return stock; }
            set { stock = value; }
        }


        //public DataTable getProductsInWarehouse(int option)
        //{
        //    DataTable tb = new DataTable();
        //    OpenConnection();
        //    if (Connection != null)
        //    {
        //        string queryString = "select * from Warehouse wh, Product pr, Manufacturer manu where pr.id=wh.idProduct and manu.id=wh.idManufacturer";
        //        try
        //        {
        //            SqlDataAdapter adapter = new SqlDataAdapter(queryString, Connection);
        //            adapter.Fill(tb);
        //        }
        //        catch (SqlException)
        //        {
        //            tb = new DataTable();
        //        }
        //        CloseConnection();
        //    }
        //    return tb;
        //}

        public DataTable getProductsInWarehouse(int month, int year)
        {
            DataTable tb = new DataTable();
            OpenConnection();
            if (Connection != null)
            {
                string queryString = "select * from Warehouse wh, Product pr, Manufacturer manu" +
                " where pr.id=wh.idProduct and manu.id=wh.idManufacturer and MONTH(wh.datetime)=" + month.ToString();
                try
                {
                    SqlDataAdapter adapter = new SqlDataAdapter(queryString, Connection);
                    adapter.Fill(tb);
                }
                catch (SqlException)
                {
                    tb = new DataTable();
                }
                CloseConnection();
            }
            return tb;
        }

        // get all months are contained in column "datetime" of table "warehouse"
        public List<int> GetAllMonths()
        {
            List<int> months = new List<int>();
            OpenConnection();
            if (Connection != null)
            {
                string queryString = "select DISTINCT MONTH(datetime) as month from Warehouse order by month asc";
                try
                {
                    SqlDataAdapter adapter = new SqlDataAdapter(queryString, Connection);
                    DataTable tb = new DataTable();
                    adapter.Fill(tb);
                    if (tb.Rows.Count != 0)
                    {
                        foreach (DataRow row in tb.Rows)
                        {
                            int month = (int)row[0];
                            months.Add(month);
                        }
                    }
                }
                catch (SqlException)
                {
                    months = new List<int>();
                }
                CloseConnection();
            }
            return months;
        }

        // get all years are contained in column "datetime" of table "warehouse"
        public List<int> GetAllYears()
        {
            List<int> years = new List<int>();
            OpenConnection();
            if (Connection != null)
            {
                string queryString = "select DISTINCT YEAR(datetime) as year from Warehouse order by year asc";
                try
                {
                    SqlDataAdapter adapter = new SqlDataAdapter(queryString, Connection);
                    DataTable tb = new DataTable();
                    adapter.Fill(tb);
                    if (tb.Rows.Count != 0)
                    {
                        foreach (DataRow row in tb.Rows)
                        {
                            int month = (int)row[0];
                            years.Add(month);
                        }
                    }
                }
                catch (SqlException)
                {
                    years = new List<int>();
                }
                CloseConnection();
            }
            return years;
        }

    }
}